﻿using MLSMooc.BO;
using MLSMooc.DAO.Interface.Discussion;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MLSMooc.Helper;
using System.Data.SqlClient;

namespace MLSMooc.DAO.Implement.Discussion
{
    public class DiscussionDAO : IDiscussionDAO
    {
        private static DiscussionDAO instance = new DiscussionDAO();
        private DiscussionDAO() { }
        public static DiscussionDAO Getinstance() => instance;

        public async Task<IEnumerable<DiscussionReplyBO>> GetDiscussionReplyList()
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append($@"select mcdr.Id as mcdrId,mcdr.DiscussionId,mcdr.AdminId as mcdrAdminId ,a.LinkName as aLinkName,mcdr.Content ,mcdr.ReplyAdminId,aa.LinkName as aaLinkName ,mcdr.CreateTime,ar.RoleName,arr.RoleName as arrRoleName from Mooc_Course_DiscussionReply mcdr  join Admin a on a.Id=mcdr.AdminId  join Admin aa on aa.Id=mcdr.ReplyAdminId join Admin_Role ar on ar.Id=a.RoleId join Admin_Role arr on aa.RoleId=arr.Id  where mcdr.isDelete=0 order by mcdr.Id");
                var res = await conn.QueryAsync<DiscussionReplyBO>(sql.ToString());
                return res;
            }
        }

        public async Task<IEnumerable<DiscussionBO>> GetByIdDiscussionList(int id)
        {
            using (SqlConnection conn=new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append($@"select mcd.Id as mcdId,mcd.CourseId,mcd.Content as mcdContent,mcd.CreateTime,a.Id as aId,a.LinkName,ar.RoleName from Mooc_Course_Discussion mcd join Admin a on a.Id=mcd.AdminId join Admin_Role ar on ar.Id=a.RoleId where  mcd.isDelete=0 and CourseId=@id order by mcd.CreateTime desc");
                var res = await conn.QueryAsync<DiscussionBO>(sql.ToString(), new { id = id });
                return res;
            }
        }
        public async Task<IEnumerable<DiscussionBO>> GetByCourseIdDiscussionList(int courseid)
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append($@"select  CourseId, AdminId,count(AdminId) cishu from Mooc_Course_Discussion 
   where CourseId=@CourseId and isDelete=0  group by CourseId,AdminId");
                var res = await conn.QueryAsync<DiscussionBO>(sql.ToString(), new { CourseId = courseid });
                return res;
            }
        }
        public async Task<IEnumerable<DiscussionBO>> GetByCourseIdRDiscussionList(string rid)
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append($@"select AdminId, count(AdminId) huifucishu from Mooc_Course_DiscussionReply where DiscussionId in({rid})  group by AdminId");
                var res = await conn.QueryAsync<DiscussionBO>(sql.ToString());
                return res;
            }
        }
        public async Task<IEnumerable<DiscussionBO>> GetDiscussionReplyByCourseId(int corseid)
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append($@"select Id from Mooc_Course_Discussion where CourseId={corseid} and isDelete=0");
                var res = await conn.QueryAsync<DiscussionBO>(sql.ToString());
                return res;
            }
        }

        public async Task<IEnumerable<BrowseBO>> GetUserNamerowseList()
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                //string str = string.Empty;

                //if (!string.IsNullOrEmpty(name.Trim()))
                //{
                //    str = " and (a.LinkName like '" + name + "%' or a.adminName like '" + name + "%')";
                //}
                StringBuilder sql = new StringBuilder($@"select DISTINCT a.LinkName as AName,a.id as Id,a.AdminName  from Admin a join Admin_Role r on a.RoleId=r.Id where a.isDelete=0 order by AdminName");
                var res = await conn.QueryAsync<BrowseBO>(sql.ToString());
                return res;
            }
        }

        public async Task<IEnumerable<DiscussionReplyBO>> GetByIdDiscussionReplyList(int id)
        {
            using (SqlConnection conn=new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append($@"select mcdr.Id as mcdrId,mcdr.DiscussionId,mcdr.AdminId as mcdrAdminId ,a.LinkName as aLinkName,mcdr.Content ,mcdr.ReplyAdminId,aa.LinkName as aaLinkName ,mcdr.CreateTime,ar.RoleName,arr.RoleName as arrRoleName from Mooc_Course_DiscussionReply mcdr  join Admin a on a.Id=mcdr.AdminId  join Admin aa on aa.Id=mcdr.ReplyAdminId join Admin_Role ar on ar.Id=a.RoleId join Admin_Role arr on aa.RoleId=arr.Id  where mcdr.DiscussionId=@id and mcdr.isDelete=0 order by mcdr.Id");
                var res = await conn.QueryAsync<DiscussionReplyBO>(sql.ToString(), new { id = id });
                return res;
            }
        }
    }
}
